--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_TERM_GUAR_LOAB.HQL
--    Functions : 表13：贷款余额担保方式期限结构表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-11  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_TERM_GUAR_LOAB PARTITION (DATA_DATE = '20180331')
-- 除了合计之外
    SELECT
     AREA.AREA_CODE_4                                                                        AS FIN_ORG_DIST
    ,YE.CCY                                                                                  AS CCY
    ,GUAR.LOAN_GUAR_TYPE_ID_1                                                                AS GUARANTY_TYPE  -- 贷款担保方式 显示一级
    ,GUAR.LOAN_GUAR_TYPE_DSCR_1                                                              AS TYPE_DESC
    ,SUM(COALESCE(CASE WHEN YE.TERM_CODE ='1' THEN YE.ACTUAL_BAL ELSE 0 END,0))/100000000    AS BAL_0_6M       --6个月（含）以内-贷款余额
    ,SUM(COALESCE(CASE WHEN YE.TERM_CODE ='1' THEN YE.WSUM_BAL   ELSE 0 END,0))/100000000    AS WSUM_0_6M      --6个月（含）以内-加权金额
    ,SUM(COALESCE(CASE WHEN YE.TERM_CODE ='2' THEN YE.ACTUAL_BAL ELSE 0 END,0))/100000000    AS BAL_6_12M      --6个月至1年（含）-贷款余额
    ,SUM(COALESCE(CASE WHEN YE.TERM_CODE ='2' THEN YE.WSUM_BAL   ELSE 0 END,0))/100000000    AS WSUM_6_12M     --6个月至1年（含）-加权金额
    ,SUM(COALESCE(CASE WHEN YE.TERM_CODE ='3' THEN YE.ACTUAL_BAL ELSE 0 END,0))/100000000    AS BAL_1_3Y       --1至3年（含）-贷款余额
    ,SUM(COALESCE(CASE WHEN YE.TERM_CODE ='3' THEN YE.WSUM_BAL   ELSE 0 END,0))/100000000    AS WSUM_1_3Y      --1至3年（含）-加权金额
    ,SUM(COALESCE(CASE WHEN YE.TERM_CODE ='4' THEN YE.ACTUAL_BAL ELSE 0 END,0))/100000000    AS BAL_3_5Y       --3至5年（含）-贷款余额
    ,SUM(COALESCE(CASE WHEN YE.TERM_CODE ='4' THEN YE.WSUM_BAL   ELSE 0 END,0))/100000000    AS WSUM_3_5Y      --3至5年（含）-加权金额
    ,SUM(COALESCE(CASE WHEN YE.TERM_CODE ='5' THEN YE.ACTUAL_BAL ELSE 0 END,0))/100000000    AS BAL_5_10Y      --5至10年（含）-贷款余额
    ,SUM(COALESCE(CASE WHEN YE.TERM_CODE ='5' THEN YE.WSUM_BAL   ELSE 0 END,0))/100000000    AS WSUM_5_10Y     --5至10年（含）-加权金额
    ,SUM(COALESCE(CASE WHEN YE.TERM_CODE ='6' THEN YE.ACTUAL_BAL ELSE 0 END,0))/100000000    AS BAL_10_999Y    --10年以上-贷款余额
    ,SUM(COALESCE(CASE WHEN YE.TERM_CODE ='6' THEN YE.WSUM_BAL   ELSE 0 END,0))/100000000    AS WSUM_10_999Y   --10年以上-加权金额
    ,SUM(COALESCE(YE.ACTUAL_BAL,0))/100000000                                                AS BAL_ALL        --所有期限-贷款余额
    ,SUM(COALESCE(YE.WSUM_BAL,0))/100000000                                                  AS WSUM_ALL       --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_LOAB_SUM WHERE DATA_DATE='20180331')YE
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON YE.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE
    -- 关联担保方式维度表 取一级担保方式
    LEFT JOIN DIMENSION.DIM_LOAN_GUARANTY_TYPE GUAR ON YE.GUARANTY_TYPE=GUAR.LOAN_GUAR_TYPE_ID_2 AND '20180331' BETWEEN GUAR.START_DATE AND GUAR.END_DATE
    GROUP BY AREA.AREA_CODE_4
            ,YE.CCY
            ,GUAR.LOAN_GUAR_TYPE_ID_1
            ,GUAR.LOAN_GUAR_TYPE_DSCR_1

    UNION ALL

    -- 合计
    SELECT
     AREA.AREA_CODE_4                                                                            AS FIN_ORG_DIST
    ,LOAB.CCY                                                                                    AS CCY
    ,'G'                                                                                         AS GUARANTY_TYPE  -- 贷款担保方式 显示一级
    ,'合计'                                                                                      AS TYPE_DESC
    ,SUM(COALESCE(CASE WHEN LOAB.TERM_CODE ='1' THEN LOAB.ACTUAL_BAL ELSE 0 END,0))/100000000    AS BAL_0_6M       --6个月（含）以内-贷款余额
    ,SUM(COALESCE(CASE WHEN LOAB.TERM_CODE ='1' THEN LOAB.WSUM_BAL   ELSE 0 END,0))/100000000    AS WSUM_0_6M      --6个月（含）以内-加权金额
    ,SUM(COALESCE(CASE WHEN LOAB.TERM_CODE ='2' THEN LOAB.ACTUAL_BAL ELSE 0 END,0))/100000000    AS BAL_6_12M      --6个月至1年（含）-贷款余额
    ,SUM(COALESCE(CASE WHEN LOAB.TERM_CODE ='2' THEN LOAB.WSUM_BAL   ELSE 0 END,0))/100000000    AS WSUM_6_12M     --6个月至1年（含）-加权金额
    ,SUM(COALESCE(CASE WHEN LOAB.TERM_CODE ='3' THEN LOAB.ACTUAL_BAL ELSE 0 END,0))/100000000    AS BAL_1_3Y       --1至3年（含）-贷款余额
    ,SUM(COALESCE(CASE WHEN LOAB.TERM_CODE ='3' THEN LOAB.WSUM_BAL   ELSE 0 END,0))/100000000    AS WSUM_1_3Y      --1至3年（含）-加权金额
    ,SUM(COALESCE(CASE WHEN LOAB.TERM_CODE ='4' THEN LOAB.ACTUAL_BAL ELSE 0 END,0))/100000000    AS BAL_3_5Y       --3至5年（含）-贷款余额
    ,SUM(COALESCE(CASE WHEN LOAB.TERM_CODE ='4' THEN LOAB.WSUM_BAL   ELSE 0 END,0))/100000000    AS WSUM_3_5Y      --3至5年（含）-加权金额
    ,SUM(COALESCE(CASE WHEN LOAB.TERM_CODE ='5' THEN LOAB.ACTUAL_BAL ELSE 0 END,0))/100000000    AS BAL_5_10Y      --5至10年（含）-贷款余额
    ,SUM(COALESCE(CASE WHEN LOAB.TERM_CODE ='5' THEN LOAB.WSUM_BAL   ELSE 0 END,0))/100000000    AS WSUM_5_10Y     --5至10年（含）-加权金额
    ,SUM(COALESCE(CASE WHEN LOAB.TERM_CODE ='6' THEN LOAB.ACTUAL_BAL ELSE 0 END,0))/100000000    AS BAL_10_999Y    --10年以上-贷款余额
    ,SUM(COALESCE(CASE WHEN LOAB.TERM_CODE ='6' THEN LOAB.WSUM_BAL   ELSE 0 END,0))/100000000    AS WSUM_10_999Y   --10年以上-加权金额
    ,SUM(COALESCE(LOAB.ACTUAL_BAL,0))/100000000                                                  AS BAL_ALL        --所有期限-贷款余额
    ,SUM(COALESCE(LOAB.WSUM_BAL,0))/100000000                                                    AS WSUM_ALL       --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_LOAB_SUM WHERE DATA_DATE='20180331') LOAB
   -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON LOAB.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE
    GROUP BY AREA.AREA_CODE_4,LOAB.CCY;